package com.ratingbay.console.dao;

import java.util.List;
import java.sql.Timestamp;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.data.jpa.repository.Query;

import com.ratingbay.console.model.Game;
import com.ratingbay.console.model.Link;
import com.ratingbay.console.model.RbGameTweetTrend;
import com.ratingbay.console.model.TweetLink;

public interface LinkDAO extends JpaRepository<Link, Long> {
	@Query("select l from Link l where l.type = ?1")
	public Page<Link> findByType(int type, Pageable pageable);
	@Query("select l from Link l where l.type = ?1 and l.mediaDirectLink <> ?2")
	public Page<Link> findByTypeWithMDLnot(int type, String mediaDirectLink, Pageable pageable);
	
	@Query(value = "select * from link where id in(select min(id) from link where id in(select id from link t where t.tweet_count >1000 and t.type = 0 and id_game in(select id from game where status =1)) and "
			+ "subString(media_direct_link,0,19) = 'http://www.youtube' GROUP BY subString(media_direct_link,0,41) order by max(tweet_count) desc ) "
			+ "order by tweet_count desc limit ?2",
			nativeQuery = true)
	public List<Link> findByTypeWithMDLnotOrderByTweetCountDesc(int type, int pageSize);
	//home page video link

	@Query("select l from Link l where l.type=?1 and l.idGame=?2")
	public Page<Link> findByTypeAndGame(int type, Long idGame, Pageable pageable);
	
	@Query(value = "select l.* from link l where l.id in(select min(t.id) from "
			+ "(select * from link i where i.type = ?1 and i.id_game = ?2 order by tweet_count desc limit 1000) t "
			+ "where subString(t.media_direct_link,0,19) = 'http://www.youtube' "
			+ "group by subString(t.media_direct_link,0,41) order by max(t.tweet_count) desc limit 500) order by tweet_count desc,id limit ?3 offset ?3*?4",
			nativeQuery = true)
	public List<Link> findVideoByGameGroupByMedioDirectLinkOrderByTweetCountDesc(int type, Long idGame, int pageSize,int pageNum);
	//game page video link
	/**select * from rb_link where id_game = ?2 and type = ?1 order by tweet_count desc limit ?3 offset ?3*?4*/
	@Query(value = "select l.* from link l where l.id in(select min(t.id) from "
			+ "(select * from link i where i.type = ?1 and i.id_game = ?2 order by tweet_count desc limit 1000) t "
			+ "where t.media_direct_link is not null "
			+ "group by media_direct_link order by max(t.tweet_count) desc limit 500) order by tweet_count desc,id limit ?3 offset ?3*?4",
			nativeQuery = true)
	public List<Link> findByGameGroupByMedioDirectLinkOrderByTweetCountDesc(int type, Long idGame, int pageSize,int pageNum);
	//game page link and image link
	
	@Query(value ="select count(distinct media_Direct_Link) from link l where l.type = ?1 and l.id_Game = ?2 and l.media_Direct_Link is not null",
			nativeQuery=true)
	public Long findCountByTypeAndGame(int type ,Long idGame);

	@Query("select l from Link l where l.type=?1 and l.id in ?2")
	public Page<Link> findByTypeAndLinkIds(int type, List<Long> idLinks, Pageable pageable);
	@Query("select l from Link l where l.type=?1 and l.id in ?2 and l.mediaDirectLink <> ?3")
	public Page<Link> findByTypeAndLinkIdsWithMDLnot(int type, List<Long> idLinks, 
		String mediaDirectLink, Pageable pageable);

    @Query("select l from Link l where l.type=?1 and l.idGame=?2 and l.id in ?3")
	public Page<Link> findByTypeAndGameAndLinkIds(int type, Long idGame, 
		List<Long> idLinks, Pageable pageable);
    
     @Query(value = "select * from link where id in (select min(a.id) from "
    		+ " (select  distinct l.* from link l inner join tweet_link tl on tl.id_link=l.id "
    		+ " inner join tweet t on t.id=tl.id_tweet and t.user_id= ?3 and l.type= ?1 and l.id_game=?2 and media_direct_link is not null"
    		+ " order by tweet_count desc limit 1000) a group by a.media_direct_link limit ?4) order by tweet_count desc",
			nativeQuery = true)
	public List<Link> findByTypeAndGameAndLinkIdsGroupByTweetCountDesc(int type, Long idGame, Long userId,int pageSize);
     
     @Query("select l from Link l where l.id=?1 ")
 	public List<Link> findById(Long id);
}
